from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, delete
from app.db.postgres import get_session
from app.models import Evaluation, EvaluationItemScore
from app.schemas.evaluation import (
    EvaluationCreate,
    EvaluationRead,
    EvaluationItemScoreCreate,
    EvaluationItemScoreRead,
)
from app.services.scoring import compute_evaluation_total


router = APIRouter(prefix="/evaluations", tags=["evaluations"])


@router.get("", response_model=list[EvaluationRead])
async def list_evaluations(session: AsyncSession = Depends(get_session)):
    result = await session.execute(select(Evaluation))
    return [EvaluationRead.model_validate(x) for x in result.scalars().all()]


@router.post("", response_model=EvaluationRead)
async def create_evaluation(payload: EvaluationCreate, session: AsyncSession = Depends(get_session)):
    obj = Evaluation(**payload.model_dump())
    session.add(obj)
    await session.flush()
    await session.refresh(obj)
    await session.commit()
    return EvaluationRead.model_validate(obj)


@router.post("/{evaluation_id}/scores", response_model=EvaluationItemScoreRead)
async def add_score(evaluation_id: str, payload: EvaluationItemScoreCreate, session: AsyncSession = Depends(get_session)):
    ev = await session.get(Evaluation, evaluation_id)
    if not ev:
        raise HTTPException(status_code=404, detail="评估不存在")
    data = payload.model_dump()
    data["evaluation_id"] = evaluation_id
    result = await session.execute(
        select(EvaluationItemScore).where(
            EvaluationItemScore.evaluation_id == evaluation_id,
            EvaluationItemScore.code == data["code"]
        )
    )
    obj = result.scalars().first()
    if obj:
        obj.score_value = data.get("score_value")
        _v = data.get("is_veto", False)
        obj.is_veto = _v if isinstance(_v, bool) else (str(_v).strip().lower() in ("true","1","yes","y","on"))
        obj.comment = data.get("comment")
        await session.flush()
        veto_check = await session.execute(
            select(EvaluationItemScore).where(
                EvaluationItemScore.evaluation_id == evaluation_id,
                EvaluationItemScore.is_veto == True
            )
        )
        ev.verdict = veto_check.scalars().first() is not None
        await session.refresh(obj)
        await session.refresh(ev)
        await session.commit()
        return EvaluationItemScoreRead.model_validate(obj)
    obj = EvaluationItemScore(**data)
    session.add(obj)
    await session.flush()
    veto_check = await session.execute(
        select(EvaluationItemScore).where(
            EvaluationItemScore.evaluation_id == evaluation_id,
            EvaluationItemScore.is_veto == True
        )
    )
    ev.verdict = veto_check.scalars().first() is not None
    await session.refresh(obj)
    await session.refresh(ev)
    await session.commit()
    return EvaluationItemScoreRead.model_validate(obj)


@router.get("/{evaluation_id}/scores", response_model=list[EvaluationItemScoreRead])
async def list_scores(evaluation_id: str, session: AsyncSession = Depends(get_session)):
    result = await session.execute(select(EvaluationItemScore).where(EvaluationItemScore.evaluation_id == evaluation_id))
    return [EvaluationItemScoreRead.model_validate(x) for x in result.scalars().all()]


@router.delete("/{evaluation_id}/scores")
async def clear_scores(evaluation_id: str, session: AsyncSession = Depends(get_session)):
    ev = await session.get(Evaluation, evaluation_id)
    if not ev:
        raise HTTPException(status_code=404, detail="评估不存在")
    del_res = await session.execute(
        delete(EvaluationItemScore).where(EvaluationItemScore.evaluation_id == evaluation_id)
    )
    ev.score_total = 0
    ev.verdict = False
    ev.level = None
    await session.flush()
    await session.refresh(ev)
    await session.commit()
    return {"deleted": del_res.rowcount or 0}


@router.post("/{evaluation_id}/compute-total", response_model=EvaluationRead)
async def compute_total(evaluation_id: str, session: AsyncSession = Depends(get_session)):
    ev = await session.get(Evaluation, evaluation_id)
    if not ev:
        raise HTTPException(status_code=404, detail="评估不存在")
    total = await compute_evaluation_total(session, evaluation_id)
    ev.score_total = total
    # 是否命中否决项
    veto_q = await session.execute(
        select(EvaluationItemScore).where(
            EvaluationItemScore.evaluation_id == evaluation_id,
            EvaluationItemScore.is_veto == True
        )
    )
    has_veto = veto_q.scalars().first() is not None
    ev.verdict = has_veto
    # 等级评定
    level = None
    if not has_veto:
        if total >= 85:
            level = 'excellent'
        elif total >= 70:
            level = 'good'
        elif total >= 60:
            level = 'pass'
        else:
            level = 'fail'
    else:
        level = 'fail'
    ev.level = level
    await session.flush()
    await session.refresh(ev)
    await session.commit()
    return EvaluationRead.model_validate(ev)


@router.get("/latest", response_model=EvaluationRead)
async def latest_by_activity(activity_id: str, session: AsyncSession = Depends(get_session)):
    result = await session.execute(
        select(Evaluation)
        .where(Evaluation.activity_id == activity_id)
        .order_by(Evaluation.version_no.desc(), Evaluation.created_at.desc())
        .limit(1)
    )
    obj = result.scalars().first()
    if not obj:
        raise HTTPException(status_code=404, detail="暂无评估")
    return EvaluationRead.model_validate(obj)


@router.post("/upsert", response_model=EvaluationRead)
async def upsert_evaluation(payload: EvaluationCreate, session: AsyncSession = Depends(get_session)):
    try:
        result = await session.execute(
            select(Evaluation).where(
                Evaluation.activity_id == payload.activity_id,
                Evaluation.version_no == payload.version_no
            )
        )
    except Exception as e:
        from sqlalchemy.exc import NotSupportedError
        if isinstance(e, NotSupportedError) or 'InvalidCachedStatementError' in str(e):
            await session.rollback()
            result = await session.execute(
                select(Evaluation).where(
                    Evaluation.activity_id == payload.activity_id,
                    Evaluation.version_no == payload.version_no
                )
            )
        else:
            raise
    obj = result.scalars().first()
    if obj:
        return EvaluationRead.model_validate(obj)
    obj = Evaluation(**payload.model_dump())
    session.add(obj)
    await session.flush()
    await session.refresh(obj)
    await session.commit()
    return EvaluationRead.model_validate(obj)
